package utils;

import java.sql.*;
import java.util.*;
import model.LogisticsHighlight;

public class DatabaseManager {
    private static final Scanner scanner = new Scanner(System.in);

    public static void main(String[] args) {
        while (true) {
            showMainMenu();
            int choice = getChoice();

            switch (choice) {
                case 1:
                    showTableList();
                    break;
                case 2:
                    showTableData();
                    break;
                case 3:
                    reinitializeDatabase();
                    break;
                case 4:
                    generateMockData();
                    break;
                case 0:
                    System.out.println("感谢使用，再见！");
                    return;
                default:
                    System.out.println("无效的选择，请重试。");
            }
        }
    }

    private static void showMainMenu() {
        System.out.println("\n=== 数据库管理系统 ===");
        System.out.println("1. 查看所有表");
        System.out.println("2. 查看表数据");
        System.out.println("3. 重新初始化数据库");
        System.out.println("4. 生成模拟数据");
        System.out.println("0. 退出");
        System.out.print("请选择操作: ");
    }

    private static int getChoice() {
        try {
            return Integer.parseInt(scanner.nextLine());
        } catch (NumberFormatException e) {
            return -1;
        }
    }

    public static void reinitializeDatabase() {
        System.out.println("\n警告：此操作将删除所有数据！");
        System.out.print("确定要删除所有表并重新创建吗？(y/n): ");
        String response = scanner.nextLine().trim().toLowerCase();
        
        if (response.equals("y")) {
            try {
                // 删除所有表
                System.out.println("正在删除所有表...");
                dropAllTables();

                // 重新创建表
                System.out.println("正在创建新表...");
                createAllTables();

                System.out.println("数据库初始化完成！");
            } catch (Exception e) {
                System.out.println("初始化数据库时出错: " + e.getMessage());
                e.printStackTrace();
            }
        } else {
            System.out.println("操作已取消。");
        }
    }

    private static void dropAllTables() {
        List<String> tableNames = new ArrayList<>();
        
        // 首先获取所有表名
        try (Connection conn = DBUtil.connect();
             ResultSet tables = conn.getMetaData().getTables(null, null, "%", new String[]{"TABLE"})) {
            while (tables.next()) {
                tableNames.add(tables.getString("TABLE_NAME"));
            }
        } catch (SQLException e) {
            throw new RuntimeException("获取表列表时出错: " + e.getMessage(), e);
        }

        // 然后尝试删除每个表，如果失败则重试
        for (String tableName : tableNames) {
            boolean success = false;
            int retries = 3;
            
            while (!success && retries > 0) {
                try (Connection conn = DBUtil.connect();
                     Statement stmt = conn.createStatement()) {
                    
                    // 首先终止可能的活动连接
                    stmt.execute("PRAGMA busy_timeout = 5000");
                    
                    // 尝试删除表
                    stmt.executeUpdate("DROP TABLE IF EXISTS " + tableName);
                    System.out.println("已删除表: " + tableName);
                    success = true;
                    
                } catch (SQLException e) {
                    retries--;
                    if (retries > 0) {
                        System.out.println("删除表 " + tableName + " 时遇到锁定，正在重试... （还剩 " + retries + " 次尝试）");
                        try {
                            Thread.sleep(1000); // 等待1秒后重试
                        } catch (InterruptedException ie) {
                            Thread.currentThread().interrupt();
                            throw new RuntimeException("线程被中断", ie);
                        }
                    } else {
                        throw new RuntimeException("删除表 " + tableName + " 时出错: " + e.getMessage(), e);
                    }
                }
            }
        }
    }

    private static void createAllTables() {
        try {
            // 设置较长的超时时间
            try (Connection conn = DBUtil.connect();
                 Statement stmt = conn.createStatement()) {
                stmt.execute("PRAGMA busy_timeout = 5000");
            }
            
            // 创建所有表
            DBUtil.createTableIfNotExists();
            
            System.out.println("所有表创建成功！");
        } catch (Exception e) {
            throw new RuntimeException("创建表时出错: " + e.getMessage(), e);
        }
    }

    private static void showTableList() {
        try (Connection conn = DBUtil.connect();
             ResultSet tables = conn.getMetaData().getTables(null, null, "%", new String[]{"TABLE"})) {
            
            System.out.println("\n=== 数据库表列表 ===");
            int count = 0;
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                int rowCount = getTableRowCount(conn, tableName);
                System.out.printf("%d. %s (记录数: %d)\n", ++count, tableName, rowCount);
            }
            
            if (count == 0) {
                System.out.println("数据库中没有表。");
            }
        } catch (SQLException e) {
            System.out.println("获取表列表时出错: " + e.getMessage());
        }
    }

    private static int getTableRowCount(Connection conn, String tableName) {
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName)) {
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            System.out.println("获取表 " + tableName + " 记录数时出错: " + e.getMessage());
        }
        return 0;
    }

    private static void showTableData() {

        List<String> tableNames = new ArrayList<>();
        
        try (Connection conn = DBUtil.connect();
             ResultSet tables = conn.getMetaData().getTables(null, null, "%", new String[]{"TABLE"})) {
            
            System.out.println("\n=== 选择要查看的表 ===");
            int count = 0;
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                tableNames.add(tableName);
                System.out.printf("%d. %s\n", ++count, tableName);
            }
            
            if (count == 0) {
                System.out.println("数据库中没有表。");
                return;
            }

            System.out.print("请输入表编号: ");
            int choice = getChoice();
            
            if (choice < 1 || choice > count) {
                System.out.println("无效的表编号。");
                return;
            }

            String selectedTable = tableNames.get(choice - 1);
            displayTableData(conn, selectedTable);
            
        } catch (SQLException e) {
            System.out.println("查看表数据时出错: " + e.getMessage());
        }
    }

    private static void displayTableData(Connection conn, String tableName) {
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName)) {
            
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            
            // 打印表头
            System.out.println("\n=== " + tableName + " 表数据 ===");
            for (int i = 1; i <= columnCount; i++) {
                System.out.printf("%-20s", metaData.getColumnName(i));
            }
            
            // 打印分隔线
            System.out.println();
            StringBuilder separator = new StringBuilder();
            for (int i = 0; i < columnCount * 20; i++) {
                separator.append("=");
            }
            System.out.println(separator.toString());
            
            // 打印数据
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    System.out.printf("%-20s", rs.getString(i));
                }
                System.out.println();
            }
            
        } catch (SQLException e) {
            System.out.println("显示表 " + tableName + " 数据时出错: " + e.getMessage());
        }
    }

    private static void generateMockData() {
        System.out.println("\n=== 生成模拟数据 ===");
        System.out.println("1. 物流运输要求表");
        System.out.println("2. 冷链物流配送需求表");
        System.out.println("0. 返回");
        System.out.print("请选择要生成的表: ");

        int tableChoice = getChoice();
        if (tableChoice == 0) return;

        System.out.print("请输入要生成的记录数量: ");
        int count = getChoice();
        if (count <= 0) {
            System.out.println("数量必须大于0");
            return;
        }

        try {
            switch (tableChoice) {
                case 1:
                    generateLogisticsRequirements(count);
                    break;
                case 2:
                    generateLogisticsInfo(count);
                    break;
                default:
                    System.out.println("无效的选择");
            }
        } catch (Exception e) {
            System.out.println("生成数据失败: " + e.getMessage());
        }
    }

    private static void generateLogisticsRequirements(int count) {
        List<String> items = Arrays.asList("装运费用核定", "车辆安排", "易流任务管理", "装车现场管理",
                                         "在途监控跟踪", "在途异常处理", "时效管理", "温度管理", 
                                         "物料回收", "异常情况处理", "单据回收");
        List<String> requirements = Arrays.asList(
            "提前一天在SAP系统创建装运",
            "根据装运信息提前一天安排车辆并共享车辆信息及对应司机信息",
            "装车前30分钟完成易流任务下发",
            "按全过程标准要求收集卫生、预冷、装车、封封、打冷、车辆信息等至客户群",
            "15分钟在途跟踪一次，有异常5分钟内报备反馈",
            "在途200公里跟踪，全程异常信息共享",
            "根据对应客户时效要求、在客流小程序每车次车辆到达强认",
            "在途温度全程要求90%以上，到达离站确认温度合格再操作卸货和达到离开",
            "温控设备、回单、餐饮次日回收，托盘按规定时间回收，并共享清单明细",
            "交通事故、交通管制、少货、缺损、温度异常、拒收等，10分钟内反馈情况，30分钟内启动应急预案",
            "次月7号前按制度要求，整理并提交纸质回单"
        );
        List<String> responsibles = Arrays.asList("承运商调度", "承运商调度", "承运业务员", "承运业务员",
                                                "承运业务员", "承运业务员", "司机", "司机",
                                                "承运业务员", "承运商负责人", "承运业务员");
        List<String> supervisors = Arrays.asList("物流主管", "运管", "承运商调度", "当班保管",
                                               "运管", "运管", "承运商业务员", "承运商业务员",
                                               "运管", "运管", "统计员");

        try {
            Connection conn = DBUtil.connect();
            String sql = "INSERT INTO logistics_requirement (item, requirement, responsible_person, supervisor) VALUES (?, ?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);

            for (int i = 0; i < count; i++) {
                int index = i % items.size();
                pstmt.setString(1, items.get(index));
                pstmt.setString(2, requirements.get(index));
                pstmt.setString(3, responsibles.get(index));
                pstmt.setString(4, supervisors.get(index));
                pstmt.executeUpdate();
            }
            System.out.println("成功生成 " + count + " 条物流运输要求数据");
        } catch (SQLException e) {
            System.out.println("生成数据失败: " + e.getMessage());
        }
    }

    private static void generateLogisticsInfo(int count) {
        List<String> customerNames = Arrays.asList(
            "永辉超市东莞配送中心",
            "华润万家深圳物流中心",
            "大润发广州配送中心",
            "沃尔玛佛山生鲜仓",
            "天虹商场中山物流中心"
        );
        List<String> addresses = Arrays.asList(
            "广东省东莞市麻涌镇新沙路普洛斯东莞新沙物流园",
            "深圳市宝安区福永镇怀德港物流中心",
            "广州市白云区石井镇鸦岗物流园",
            "佛山市南海区狮山镇小塘物流园",
            "中山市火炬开发区东镇东二路物流园"
        );
        
        List<String> contacts = Arrays.asList(
            "张经理 13800138000",
            "李主管 13900139000",
            "王主任 13700137000",
            "陈经理 13600136000",
            "刘主管 13500135000"
        );
        
        List<String> timeInputs = Arrays.asList(
            "每日上午9:00前到达",
            "每日14:00前到达",
            "每周一、三、五上午10:00前到达",
            "每天早上7:00-9:00到达",
            "工作日下午13:00-15:00到达"
        );

        List<String> measureMethods = Arrays.asList(
            "装车前开箱抽检",
            "到达后开箱抽检",
            "全程温度监控",
            "装卸车双重抽检"
        );

        List<String> equipmentTypes = Arrays.asList(
            "冷王",
            "开利",
            "顶置独立机组",
            "德利",
            "日本三菱"
        );

        try {
            Connection conn = DBUtil.connect();
            String sql = "INSERT INTO logistics_info (name, address, km, contacts, time_input, " +
                        "truck_temp_min, truck_temp_max, product_temp_min, product_temp_max, " +
                        "measure_method, equipment_type, equipment_num, open_box, " +
                        "tray_transport, unloading_service, unloading_cost, appointment, " +
                        "document_need, stamp_confirm, replace_box, delay_accept, other_needs, " +
                        "is_first_battle_arrival) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            Random random = new Random();

            for (int i = 0; i < count; i++) {
                int index = i % customerNames.size();
                pstmt.setString(1, customerNames.get(index));
                pstmt.setString(2, addresses.get(index));
                pstmt.setInt(3, 50 + random.nextInt(250)); // 50-300km
                pstmt.setString(4, contacts.get(index));
                pstmt.setString(5, timeInputs.get(random.nextInt(timeInputs.size())));
                
                // 温度设置 (根据不同类型的商品设置不同的温度范围)
                int baseTemp = random.nextInt(3) * 5; // 0, 5, 10作为基础温度
                pstmt.setInt(6, baseTemp);     // truck_temp_min
                pstmt.setInt(7, baseTemp + 4); // truck_temp_max
                pstmt.setInt(8, baseTemp);     // product_temp_min
                pstmt.setInt(9, baseTemp + 4); // product_temp_max
                
                pstmt.setString(10, measureMethods.get(random.nextInt(measureMethods.size())));
                pstmt.setString(11, equipmentTypes.get(random.nextInt(equipmentTypes.size())));
                pstmt.setInt(12, (1 + random.nextInt(3))); // 1-3台设备
                pstmt.setString(13, random.nextBoolean() ? "是" : "否"); // 是否开箱测温
                pstmt.setString(14, random.nextBoolean() ? "是" : "否"); // 是否带托运输
                pstmt.setString(15, random.nextBoolean() ? "是" : "否"); // 是否提供卸货服务
                pstmt.setString(16, (30 + random.nextInt(21)) + "元/吨"); // 30-50元/吨
                pstmt.setString(17, random.nextBoolean() ? "是" : "否"); // 是否需要预约
                pstmt.setString(18, "装车单,签收单" + (random.nextBoolean() ? ",温度记录单" : "")); // 随车单据需求
                pstmt.setString(19, random.nextBoolean() ? "是" : "否"); // 是否盖章签收确认
                pstmt.setString(20, random.nextBoolean() ? "是" : "否"); // 是否能更换无批次纸箱
                pstmt.setString(21, random.nextBoolean() ? "是" : "否"); // 异常晚到是否可延迟收货
                pstmt.setString(22, "无特殊要求"); // 其他需求
                pstmt.setString(23, random.nextBoolean() ? "是" : "否"); // 是否首战到达

                pstmt.executeUpdate();
                
                // 获取自增主键
                ResultSet generatedKeys = pstmt.getGeneratedKeys();
                if (generatedKeys.next()) {
                    int logisticsId = generatedKeys.getInt(1);
                    
                    // 为每条物流信息生成随机标红信息
                    LogisticsHighlight highlight = new LogisticsHighlight(logisticsId);
                    
                    // 随机选择2-5个字段进行标红
                    int highlightCount = 2 + random.nextInt(4); // 2到5个标红字段
                    List<Integer> fieldIndices = new ArrayList<>();
                    for (int j = 0; j < 23; j++) {
                        fieldIndices.add(j);
                    }
                    Collections.shuffle(fieldIndices);
                    
                    // 设置随机标红字段
                    for (int j = 0; j < highlightCount; j++) {
                        int fieldIndex = fieldIndices.get(j);
                        switch (fieldIndex) {
                            case 0: highlight.setNameHighlight(1); break;
                            case 1: highlight.setAddressHighlight(1); break;
                            case 2: highlight.setKmHighlight(1); break;
                            case 3: highlight.setContactsHighlight(1); break;
                            case 4: highlight.setTimeInputHighlight(1); break;
                            case 5: highlight.setIsFirstBattleArrivalHighlight(1); break;
                            case 6: highlight.setTruckTempMinHighlight(1); break;
                            case 7: highlight.setTruckTempMaxHighlight(1); break;
                            case 8: highlight.setProductTempMinHighlight(1); break;
                            case 9: highlight.setProductTempMaxHighlight(1); break;
                            case 10: highlight.setMeasureMethodHighlight(1); break;
                            case 11: highlight.setEquipmentTypeHighlight(1); break;
                            case 12: highlight.setEquipmentNumHighlight(1); break;
                            case 13: highlight.setOpenBoxHighlight(1); break;
                            case 14: highlight.setTrayTransportHighlight(1); break;
                            case 15: highlight.setUnloadingServiceHighlight(1); break;
                            case 16: highlight.setUnloadingCostHighlight(1); break;
                            case 17: highlight.setAppointmentHighlight(1); break;
                            case 18: highlight.setDocumentNeedHighlight(1); break;
                            case 19: highlight.setStampConfirmHighlight(1); break;
                            case 20: highlight.setReplaceBoxHighlight(1); break;
                            case 21: highlight.setDelayAcceptHighlight(1); break;
                            case 22: highlight.setOtherNeedsHighlight(1); break;
                        }
                    }
                    
                    // 保存标红信息
                    DBUtil.insertLogisticsHighlight(highlight);
                }
            }
            System.out.println("成功生成 " + count + " 条冷链物流配送需求数据及其标红信息");
        } catch (SQLException e) {
            System.out.println("生成数据失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
} 